Risc World 3/Risc World 3.iso/SOFTWARE/ISSUE4/POWERBASE/DOCS/Ch05-Valid

Text File | 2001-01-27 | 16.4 KB | 300 lines

=============================================
Ch 5 − Input Validation and Validation Tables
=============================================

Powerbase can apply two kinds of data input validation:

•       Character validation
•       Table validation

5.1     Character validation
----------------------------
This means exercising control over what characters a field will accept. RISC
OS provides the means to allow writable icons to accept only certain
characters, or ranges of characters, and reject others. The disallowed
characters are simply ignored so that attempts to type them have no effect.

If you Shift/double-click on the Powerbase icon the contents of the
application directory will be displayed. In it you will see an ordinary
directory called Resources and inside Resources you will find a file called
ValStrings. You are recommended not to alter this file unless you understand
exactly what you are doing and to keep a copy of the original. Examining it
can, however, give you an insight into the validation process. Look, for
example, at the entry “03 Number”. This refers to field type 3 (Numeric).
The line below is the validation string of the writable icon which makes up
a numeric field. It looks like this:

               A0-9.+\- ;Pptr_write,4,4;KD

The initial “A” is a command which signifies “Allow” and is the part of the
validation string which especially concerns us. It determines what
characters the field will allow you to type. In this case they are the
numerals 0-9, the decimal point, plus and minus signs and <space>. Why the
back-slash before the minus sign? Because “-” is a special character in an
icon validation string (used, as in this example, to specify a range of
characters) and so are the semicolon, tilde (~) and the backslash itself. To
include any of these four characters in the validation string you must
precede it with a backslash.  The next character is a semicolon which
indicates that a new command follows.

The “P” which comes next is the command “pointer”. “ptr_write” is the name
of the sprite used to represent the mouse pointer when it is over this type
(and many other types) of field. You will have noticed that the usual RISC
OS arrow changes to a blue vertical bar, somewhat resembling the caret, when
over a writable field. “4,4” specifies the “active point” of the pointer.

A couple of further examples should be sufficient. “01 Alphanumeric” uses
the same pointer type but the characters accepted are <space> (immediately
after the “A” for “allow”), the upper-case letters A-Z, the lower-case
letters a-z, the numerals 0-9, some common punctuation and the “½”
character. You might want to add to this list. “06 Calculated” uses a
different pointer sprite (ptr_calc, which resembles a pocket calculator) and
has no “Allow” command at all. (You can’t type into this type of field; its
value is calculated from other fields.)

5.2     Validation tables
-------------------------
Table validation is used where a record field is only allowed to have
certain “values”. A manufacturing company, for instance, will have a product
code for every item it makes and a database of customers which the firm
supplies will make use of such codes to identify the items. It is essential
that whoever enters data is prevented from typing a spurious code. This can
be achieved by linking the appropriate field to a validation table. Fields
linked in this way are recognizable by having their text dark green instead
of black. Powerbase will allow you to type invalid data into the field
initially but, when you click the mouse over a new field or type Return, the
linked validation table is scanned to see whether what you entered in the
field is on the allowed list. If it isn’t Powerbase will restore the
previous contents of the field and print an error message.

Validation tables have other uses too. They can have extra columns
containing other data about the products, such as a name and a brief
description, and lists can be printed in which this more informative data
appears instead of the codes.  You can also have Powerbase replace the
typed-in code with a longer, more readable, form immediately on entry.
Provided the substitute data will fit in the field, replacement occurs when
you type Return or click the mouse in another field. If the replacement is
too long then the typed-in data will be unchanged.  This can be a great
timesaver when a lot of data has to be entered.

The F.E. college database referred to in 2.2.5 makes extensive use of
validation tables for subjects, courses, tutors and schools of origin. Not
only does this trap typing errors but the data entry is greatly speeded up
when one can type ELL instead of English Language and Literature, yet have

                                - 36 -

the latter printed out on a list by selecting the Expand switch in the Print
options window.

5.3     Creating a validation table
-----------------------------------
Choose Create table from the Validation submenu. Give the table a suitable
name and enter the number of rows required. This will be the maximum number
of items on the allowed list. (It is possible to increase the number later
if necessary.) Next place the caret in the first row (row 0) of the
scrolling list and enter the width (in characters) and the heading
(optional) for the first column of the validation table.

If the table is to be used to constrain data entry to an allowed list one
column may be all you need. The most common situation, however, is to have
two columns, the first holding the allowed list and the second being an
expansion or explanation of the former. Use the next row of the scrolling
list to enter the column width and heading for the second table column.  It
is possible to use very large and complex validation tables with all kinds
of data associated with each item on the allowed list. There is room to
specify 20 columns, but even this can be increased if necessary (see 14.6).

You can if you wish choose the foreground and background colours of both the
heading and the body of your table by clicking repeatedly with SELECT or
ADJUST on the Heading and Data icons. Whether this changes the foreground or
background colour depends on which of the two radio buttons is selected.

When you are satisfied with the data click Create and the table will be
created and displayed. It is not saved on disc at this stage.  You may enter
data into it now or later. Entries may be freely altered and overwritten.
The table will be saved when you close the database or quit Powerbase but,
if you want to play safe, click Force update on the keypad.

5.4     Linking tables to fields
--------------------------------
To link your newly-created validation table to a field click MENU over the
field and choose Link to table from the Field submenu. A pair of bump
icons, with the usual pop-up menu alternative, lets you cycle through the
tables in memory.  When you have the name of the required table displayed
choose the column of the table to which the field is to be linked. It is
recommended that column 0 normally provide the link (and therefore contains
the data items for the allowed list) and this is set by default. You may,
however, link to any column in the table and another pair of bump icons lets
you cycle through the column numbers.

Select Linked to table and click on OK. You will see that the foreground
colour of the linked field has now  changed from black to green. Place the
caret in the field and click on List values on the keypad. The validation
table should be displayed.

You may link a validation table to a scrollable list.  The link is to a
specified column of the list (make sure the pointer is over the required
column when you click MENU) and, at present, only one column may be so
linked: you can’t link two different validation tables to two columns of the
same scrollable list.

5.4.1   The Replace on entry feature

If you select this switch before linking the table a third pair of bump
icons becomes active, allowing you to choose which column of the table will
replace the data which you type in. In the F.E. college database referred to
in 5.2 the short subject codes (e.g. CHE) would be in the first column and
the longer names (e.g. Chemistry) in the second. On typing CHE <Return>
Powerbase would replace it with Chemistry. In such cases both the entries in
the allowed list proper and those in the replacement list are considered
equally valid and either may be entered. You could actually type Chemistry
if you wished, instead of just CHE, but the former would obviously take
longer to enter and you are far more likely to make a mistake, in which case
Powerbase would object.

5.4.2   When to turn off the Exact match switch

The Exact match switch is normally selected to indicate that the only user
inputs which will be accepted are those which exactly match an entry on the
allowed list.  Deselecting the switch allows you to type inputs which are
longer, but not shorter, than the entries on the allowed list so long as the
leftmost part of the input matches such an entry. The unmatched “tail” of
your typed entry will be attached to the end of the replacement string. This
feature is intended for use with the Replace on entry facility. Suppose your
database records numbers of items called Widgets, Doodahs and Thingummies.
You decide to identify these names with the codes W, D and T, put the codes
in the first column of the table and the names in the second and select

                        - 37 -

Replace on entry. You can then type W <Return> and it will be replaced with
Widgets. What you might want, however, is to type W7 and have it replaced
with Widgets, 7.  Deselecting Exact match allows you to do just that since
the W in W7 matches an entry in the validation list. The “expanded” entry in
the second column is made to read “Widgets, ” (note the comma and space) and
Powerbase tacks the unmatched part of your entry onto the end of this so
that W7 is replaced with Widgets, 7.

5.5     Displaying validation tables
------------------------------------
Placing the mouse pointer over a linked record field and double-clicking
with SELECT makes a small window pop up to the right of the field.  This
shows all the data which is on the same row of the validation table as the
linked item. The item from the allowed list is highlighted in green and the
item (if any) to be substituted on entry is shown in red.  This feature is
very useful if you are examining a database which uses coded data and you
encounter a code which you haven’t seen before.

You can also make this window appear automatically whenever the caret enters
a linked field. To turn  the feature on choose Preferences from the iconbar
menu and select the Display linked table data switch.

When linked to a field in the database record the complete table may be
displayed by placing the caret in the linked field and clicking on the
keypad button List values (F9). Alternatively, any table present in memory,
whether linked to a data field or not, may be displayed by selecting it from
the Display table submenu (reached from the Validation submenu).

5.6     The validation table menu
---------------------------------
Clicking MENU over a displayed table offers a menu with entries as follows:−

Clear removes all data from the table, leaving it blank. Since wiping out a
table in this way is pretty drastic you will be asked to confirm the
operation before it actually takes place. It is possible to recover the
table using Undo all provided the table has not been closed. When you close
a database all the validation tables in memory are written to the disc so
you will over-write your disc copy with the blank table.

Modify brings up the same window as you used to create the table. You may
then increase the number of rows, add extra columns, alter the order of
existing columns or, indeed, do anything sensible. Be careful about renaming
columns; combining this with shuffling the columns about is apt to cause
confusion: Powerbase isn’t a mind-reader! When you have made the required
changes click on  Modify.

Print outputs the contents of a validation table in the same format as a
report. The output appears in a window from which it may be saved as a text
file (see 3.1.1).

Sort <n> will sort the table on the nth column, that being the one the mouse
pointer was over when you clicked MENU on the table.

Undo change will restore the specific item which you were pointing at when
you clicked MENU to the state it was in when the table was displayed. You
cannot undo changes if you have closed the table then re-displayed it.

Undo all restores the entire table to the state it was in when first
displayed, even if Clear has been used. You cannot undo changes if you have
closed the table then re-displayed it.

Save leads to a Save box in which the default pathname points to a directory
called ValTables inside the database application directory. Since that’s
where Powerbase expects to find the tables for a database you should
normally accept this pathname by clicking on Save or typing Return. Only if
you are transferring tables from one database to another should you need to
drag the icon to a filer window.

All validation tables in memory are, in any case, saved to the ValTables
directory when you close the database or quit Powerbase and you can also
make this happen at any time by clicking on Force update on the keypad.

Save as CSV leads to a Save box from which the table contents can be saved
as a CSV file. The default pathname points to the database’s PrintJobs
directory.  The file can be loaded into a blank validation table of
appropriate format by dropping the file onto the open table (see 8.4 for
further details).

                                - 38 -

5.7     Loading validation tables
---------------------------------
A table linked to a field is automatically loaded when the database is
opened. If not yet linked, however, the table won’t be in memory unless you
have just created it. To load the table choose Show files from the
Validation submenu (Ctrl Q). The contents of the ValTables directory will be
displayed and the required tables can dragged onto the record window (or
simply double-clicked). As each table is loaded it will be displayed.

There may be times when you wishes to use a validation table to hold some
data, but don’t want to use it to validate input into a field or to link it
to a field at all. It is possible to load a validation table once the
database is open by using the procedure described in the previous paragraph,
but a more convenient solution is to add a “+” character to the end of the
table’s name (the usual limit of 10 characters applies to the name length).
Powerbase will then load it whether any field is linked to it or not.

5.8     Including validation table data in printouts
----------------------------------------------------
Validation tables are often used to allow short codes to be entered in
records but with a link to a more descriptive entry in another column of the
table.  If this is all that is required then always put the data which makes
up the actual allowed list, i.e. the items which are allowed in the fields
of the main record, into the first column of the table (column 0) and set
the link from the record field to this column. The more detailed “expanded”
entry should go in column 1. Printing with the Expand switch in the Print
options window selected  will then print the column 1 instead of the column
0 entry.

You can also include data from other columns of a table by displaying the
table  and clicking in the required columns with ADJUST, exactly like
selecting main record fields for printing (see 3.3). It is immaterial which
row you click on; only the column matters and the highlighting to show which
columns are selected always appears in the first row. The columns selected
in all tables are saved as part of a print Selection file and may therefore
be retrieved for future use. As well as highlighting the required columns
you will also need to select the Expand switch as described above. Printed
reports will then include all the data from the highlighted columns. You
will need to use this method if you want to print columns 0 and 1 of a table
instead of printing column 1 instead of column 0 as described in the
previous paragraph.

5.9     Entering validation table data into a record
----------------------------------------------------
There is one more feature of validation tables which might prove useful.  If
you place the caret in a database field and then double-click over an entry
in an open validation table, the table entry will be copied into the data
field, if it will fit. Nothing is copied if the item is too long to fit.

                                - 39 -